package org.achicu.db.engines;
/*
 * ***** BEGIN LICENSE BLOCK *****
 * Version: MPL 1.1/GPL 2.0
 * The contents of this file are subject to the Mozilla Public License
 * Version 1.1 (the "License"); you may not use this file except in
 * compliance with the License. You may obtain a copy of the License at
 * http://www.mozilla.org/MPL/
 * 
 * Software distributed under the License is distributed on an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
 * License for the specific language governing rights and limitations
 * under the License.
 * 
 * The Original Code is RhinoDB, released August 08, 2008.
 * 
 * Contributor(s): Alexandru Chiculita.
 * Alternatively, the contents of this file may be used under the terms of
 * the GNU General Public License Version 2 or later (the "GPL"), in which
 * case the provisions of the GPL are applicable instead of those above. If
 * you wish to allow use of your version of this file only under the terms of
 * the GPL and not to allow others to use your version of this file under the
 * MPL, indicate your decision by deleting the provisions above and replacing
 * them with the notice and other provisions required by the GPL. If you do
 * not delete the provisions above, a recipient may use your version of this
 * file under either the MPL or the GPL.
 * 
 * ***** END LICENSE BLOCK ***** */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Vector;

import org.achicu.db.StorageException;
import org.achicu.db.Store;
import org.achicu.db.values.StorableObject;




public class MysqlStore implements Store {

	static {
		try {
            // The newInstance() call is a work around for some
            // broken Java implementations
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception ex) {
            // handle the error
        }		
	}
	
	private Connection conn;
	
	public MysqlStore(){
		
		try {
		    conn = 
		       DriverManager.getConnection("jdbc:mysql://mysql5.hostbase.net/anvelope_buffers?" + 
		                                   "user=buffers_admin&password=kilitzak");
		    // Do something with the Connection
		} catch (SQLException ex) {
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}
		
	}
	
	
	
	public synchronized  void addCollectionItem(int collection, int objId) {
		PreparedStatement insertCollectionItemStmt = null;
		try {
			insertCollectionItemStmt = conn.prepareStatement("INSERT INTO items (collection, item) VALUES (?,?)");
			insertCollectionItemStmt.setInt(1, collection);
			insertCollectionItemStmt.setInt(2, objId);
			insertCollectionItemStmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(insertCollectionItemStmt!=null){
				try {
					insertCollectionItemStmt.close();
				} catch (SQLException e) {}
			}
		}
	}
	
	public synchronized  void setAppBuffer(int appId, int buffer) {
		PreparedStatement setAppBufferStmt = null;
		try {
			setAppBufferStmt = conn.prepareStatement("UPDATE apps SET buffer=? WHERE appId=?");
			setAppBufferStmt.setInt(1, buffer);
			setAppBufferStmt.setInt(2, appId);
			setAppBufferStmt.execute();
			if(setAppBufferStmt.getUpdateCount()==0){
				// need to add new item
				addAppBuffer(appId, buffer);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(setAppBufferStmt!=null){
				try {
					setAppBufferStmt.close();
				} catch (SQLException e) {}
			}
		}
	}
	
	private void addAppBuffer(int appId, int buffer) {
		PreparedStatement addAppBufferStmt = null;
		try {
			addAppBufferStmt = conn.prepareStatement("INSERT INTO apps (appId, buffer) VALUES ( ?, ? )");
			addAppBufferStmt.setInt(1, appId);
			addAppBufferStmt.setInt(2, buffer);
			addAppBufferStmt.execute();
		} catch (SQLException e) {
			System.out.println(e.getSQLState());
			e.printStackTrace();
		} finally {
			if(addAppBufferStmt!=null){
				try {
					addAppBufferStmt.close();
				} catch (SQLException e) {}
			}
		}
	}
	
	public synchronized  int getAppBuffer(int appId){
		PreparedStatement getAppBufferStmt = null;
		int id = -1;
		try {
			getAppBufferStmt = conn.prepareStatement("SELECT buffer FROM apps WHERE appId=?");
			getAppBufferStmt.setInt(1, appId);
			getAppBufferStmt.execute();
			ResultSet result = getAppBufferStmt.getResultSet();
			if(result.next()){
				id = result.getInt(1);
			}
		} catch (SQLException e) {
			System.out.println(e.getSQLState());
			e.printStackTrace();
		} finally {
			if(getAppBufferStmt!=null){
				try {
					getAppBufferStmt.close();
				} catch (SQLException e) {}
			}
		}
		return id;
	}

	
	public synchronized  void addCollectionItem(int collection, int objId, long index) {
		PreparedStatement insertCollectionItemStmt = null;
		try {
			insertCollectionItemStmt = conn.prepareStatement("INSERT INTO items (collection, item, integerIndex) VALUES (?,?,?)");
			insertCollectionItemStmt.setInt(1, collection);
			insertCollectionItemStmt.setInt(2, objId);
			insertCollectionItemStmt.setLong(3, index);			
			insertCollectionItemStmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(insertCollectionItemStmt!=null){
				try {
					insertCollectionItemStmt.close();
				} catch (SQLException e) {}
			}
		}
	}

	
	public synchronized  void addCollectionItem(int collection, int objId, String index) {
		PreparedStatement insertCollectionItemStmt = null;
		try {
			insertCollectionItemStmt = conn.prepareStatement("INSERT INTO items (collection, item, stringIndex) VALUES (?,?,?)");
			insertCollectionItemStmt.setInt(1, collection);
			insertCollectionItemStmt.setInt(2, objId);
			insertCollectionItemStmt.setString(3, index);			
			insertCollectionItemStmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(insertCollectionItemStmt!=null){
				try {
					insertCollectionItemStmt.close();
				} catch (SQLException e) {}
			}
		}
	}

	
	public synchronized  int createEmptyCollection(int type) {
		PreparedStatement insertCollectionStmt = null;
		int id = -1;
		try {
			insertCollectionStmt = conn.prepareStatement("INSERT INTO collections (type, parent) VALUES (?,?)");
			insertCollectionStmt.setInt(1, type);
			insertCollectionStmt.setInt(2, 0);
			insertCollectionStmt.execute();
			ResultSet result = insertCollectionStmt.getGeneratedKeys();
			if(result.next()){
				id = result.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(insertCollectionStmt!=null){
				try {
					insertCollectionStmt.close();
				} catch (SQLException e) {}
			}
		}
		return id;
	}

	
	public synchronized  byte[] getBufferById(int id) {
		byte[] buffer = null;
		PreparedStatement getBufferStmt = null;
		try {
			getBufferStmt = conn.prepareStatement("SELECT buffer FROM buffers WHERE id=?");
			getBufferStmt.setInt(1, id);
			if(getBufferStmt.execute()){
				ResultSet result = getBufferStmt.getResultSet();
				if(result.next()){
					buffer = result.getBytes(1);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(getBufferStmt!=null){
				try {
					getBufferStmt.close();
				} catch (SQLException e) {}
			}
		}
		return buffer;
	}

	
	public synchronized  int getCollectionByConfig(int inheritedCollection, String config) {
		PreparedStatement selectCollectionByConfigStmt = null;
		int id = -1;
		try {
			selectCollectionByConfigStmt = conn.prepareStatement("SELECT id FROM collections WHERE parent=? AND config=?");
			selectCollectionByConfigStmt.setInt(1, inheritedCollection);
			selectCollectionByConfigStmt.setString(2, config);
			selectCollectionByConfigStmt.execute();
			ResultSet result = selectCollectionByConfigStmt.getResultSet();
			if(result.next()){
				id = result.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(selectCollectionByConfigStmt!=null){
				try {
					selectCollectionByConfigStmt.close();
				} catch (SQLException e) {}
			}
		}
		return id;
	}

	
	public synchronized  String getCollectionConfig(int id) {
		PreparedStatement getCollectionConfigStmt = null;
		String config = null;
		try {
			getCollectionConfigStmt = conn.prepareStatement("SELECT config FROM collections WHERE id=?");
			getCollectionConfigStmt.setInt(1, id);
			getCollectionConfigStmt.execute();
			ResultSet result = getCollectionConfigStmt.getResultSet();
			if(result.next()){
				config = result.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(getCollectionConfigStmt!=null){
				try {
					getCollectionConfigStmt.close();
				} catch (SQLException e) {}
			}
		}
		return config;
	}

	
	public synchronized  Integer[] getCollectionItems(int collection, int skip, int limit,
			boolean reverse, int indexType) {
		Vector items = new Vector();
		
		PreparedStatement getCollectionItemsStmt = null;
		try {
			String sql = "";
			String order = getOrderString(reverse);
			
			switch(indexType){
				case NO_INDEX : 
					sql = "SELECT item FROM items WHERE collection=? ORDER BY item " + order + " LIMIT ?,?";
				break;
				case LONG_INDEX : 
					sql = "SELECT item FROM items WHERE collection=? ORDER BY integerIndex " + order + " LIMIT ?,?";
				break;
				case FLOAT_INDEX : 
					sql = "SELECT item FROM items WHERE collection=? ORDER BY floatIndex " + order + " LIMIT ?,?";
				break;
				case STRING_INDEX : 
					sql = "SELECT item FROM items WHERE collection=? ORDER BY stringIndex " + order + " LIMIT ?,?";
				break;
			}
			
			getCollectionItemsStmt = conn.prepareStatement(sql);
			getCollectionItemsStmt.setInt(1, collection);
			getCollectionItemsStmt.setInt(2, skip);
			getCollectionItemsStmt.setInt(3, limit);			
			getCollectionItemsStmt.execute();
			
			ResultSet result = getCollectionItemsStmt.getResultSet();
			while(result.next()){
				items.add( new Integer(result.getInt(1)) );
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(getCollectionItemsStmt!=null){
				try {
					getCollectionItemsStmt.close();
				} catch (SQLException e) {}
			}
		}
		
		Integer[] result = new Integer[items.size()];
		return (Integer[])items.toArray(result);
	}

	
	public synchronized  int getCollectionType(int id) {
		PreparedStatement getCollectionTypeStmt = null;
		int type = 0;
		try {
			getCollectionTypeStmt = conn.prepareStatement("SELECT type FROM collections WHERE id=?");
			getCollectionTypeStmt.setInt(1, id);
			getCollectionTypeStmt.execute();
			ResultSet result = getCollectionTypeStmt.getResultSet();
			if(result.next()){
				type = result.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(getCollectionTypeStmt!=null){
				try {
					getCollectionTypeStmt.close();
				} catch (SQLException e) {}
			}
		}
		return type;
	}

	
	public synchronized  Integer[] getInheritingCollections(int collection) {
		PreparedStatement getInheritedCollectionStmt = null;
		Vector vector = new Vector();
		try {
			getInheritedCollectionStmt = conn.prepareStatement("SELECT id FROM collections WHERE parent=?");
			getInheritedCollectionStmt.setInt(1, collection);
			getInheritedCollectionStmt.execute();
			ResultSet result = getInheritedCollectionStmt.getResultSet();
			while(result.next()){
				vector.add( new Integer(result.getInt(1)) );
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(getInheritedCollectionStmt!=null){
				try {
					getInheritedCollectionStmt.close();
				} catch (SQLException e) {}
			}
		}
		
		Integer []result = new Integer[vector.size()];
		return (Integer[])vector.toArray(result);
	}

	
	public synchronized  int inheritCollection(int collection, int type) {
		PreparedStatement insertCollectionStmt = null;
		int id = -1;
		try {
			insertCollectionStmt = conn.prepareStatement("INSERT INTO collections (type, parent) VALUES (?,?)");
			insertCollectionStmt.setInt(1, type);
			insertCollectionStmt.setInt(2, collection);
			insertCollectionStmt.execute();
			ResultSet result = insertCollectionStmt.getGeneratedKeys();
			if(result.next()){
				id = result.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(insertCollectionStmt!=null){
				try {
					insertCollectionStmt.close();
				} catch (SQLException e) {}
			}
		}
		return id;
	}

	
	public synchronized  int rewriteBuffer(int id, byte[] buffer) {
		PreparedStatement updateBufferStmt = null;
		try {
			updateBufferStmt = conn.prepareStatement("UPDATE buffers SET buffer=? WHERE id=?");
			updateBufferStmt.setBytes(1, buffer);
			updateBufferStmt.setInt(2, id);
			updateBufferStmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(updateBufferStmt!=null){
				try {
					updateBufferStmt.close();
				} catch (SQLException e) {}
			}
		}
		return id;
	}

	
	public synchronized  int saveBuffer(byte[] buffer) {
		PreparedStatement createBufferStmt = null;
		int id = -1;
		try {
			createBufferStmt = conn.prepareStatement("INSERT INTO buffers (buffer) VALUES (?)");
			createBufferStmt.setBytes(1, buffer);
			createBufferStmt.execute();
			ResultSet keys = createBufferStmt.getGeneratedKeys();
			if(keys.next()){
				id = keys.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(createBufferStmt!=null){
				try {
					createBufferStmt.close();
				} catch (SQLException e) {}
			}
		}
		return id;
	}

	
	public synchronized  void setCollectionConfig(int id, String config) {
		PreparedStatement updateCollectionConfigStmt = null;
		try {
			updateCollectionConfigStmt = conn.prepareStatement("UPDATE collections SET config=? WHERE id=?");
			updateCollectionConfigStmt.setString(1, config);
			updateCollectionConfigStmt.setInt(2, id);
			updateCollectionConfigStmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(updateCollectionConfigStmt!=null){
				try {
					updateCollectionConfigStmt.close();
				} catch (SQLException e) {}
			}
		}
	}


	
	public synchronized  Integer[] getCollectionItems(int collection, long value, int skip, int limit, boolean reverse) {
		Vector items = new Vector();
		
		PreparedStatement getCollectionItemsStmt = null;
		try {			
			getCollectionItemsStmt = conn.prepareStatement("SELECT item FROM items WHERE collection=? AND integerIndex = ? LIMIT ?,?");
			getCollectionItemsStmt.setInt(1, collection);
			getCollectionItemsStmt.setLong(2, value);
			getCollectionItemsStmt.setInt(3, skip);
			getCollectionItemsStmt.setInt(4, limit);			
			getCollectionItemsStmt.execute();
			
			ResultSet result = getCollectionItemsStmt.getResultSet();
			while(result.next()){
				items.add( new Integer(result.getInt(1)) );
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(getCollectionItemsStmt!=null){
				try {
					getCollectionItemsStmt.close();
				} catch (SQLException e) {}
			}
		}
		
		Integer[] result = new Integer[items.size()];
		return (Integer[])items.toArray(result);
	}
	
	
	public synchronized  Integer[] getCollectionItems(int collection, String value, int skip, int limit, boolean reverse) {
		Vector items = new Vector();
		
		PreparedStatement getCollectionItemsStmt = null;
		try {
			
			String order = getOrderString(reverse);
			
			getCollectionItemsStmt = conn.prepareStatement("SELECT item FROM items WHERE collection=? AND stringIndex = ? ORDER BY item " + order + " LIMIT ?,?");
			getCollectionItemsStmt.setInt(1, collection);
			getCollectionItemsStmt.setString(2, value);
			getCollectionItemsStmt.setInt(3, skip);
			getCollectionItemsStmt.setInt(4, limit);			
			getCollectionItemsStmt.execute();
			
			ResultSet result = getCollectionItemsStmt.getResultSet();
			while(result.next()){
				items.add( new Integer( result.getInt(1)) );
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(getCollectionItemsStmt!=null){
				try {
					getCollectionItemsStmt.close();
				} catch (SQLException e) {}
			}
		}
		
		Integer[] result = new Integer[items.size()];
		return (Integer[])items.toArray(result);
	}


	private String getOrderString(boolean reverse) {
		return reverse ? "DESC" : "ASC";
	}


	
	public synchronized  void addFloatCollectionItem(int collection, int objId, double index) {
		PreparedStatement insertCollectionItemStmt = null;
		try {
			insertCollectionItemStmt = conn.prepareStatement("INSERT INTO items (collection, item, floatIndex) VALUES (?,?,?)");
			insertCollectionItemStmt.setInt(1, collection);
			insertCollectionItemStmt.setInt(2, objId);
			insertCollectionItemStmt.setDouble(3, index);
			insertCollectionItemStmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(insertCollectionItemStmt!=null){
				try {
					insertCollectionItemStmt.close();
				} catch (SQLException e) {}
			}
		}
	}


	
	public synchronized  Integer[] getFloatCollectionItems(int collection, double value,
			int skip, int limit, boolean reverse) {
		Vector items = new Vector();
		
		PreparedStatement getCollectionItemsStmt = null;
		try {
			
			String order = getOrderString(reverse);
			
			getCollectionItemsStmt = conn.prepareStatement("SELECT item FROM items WHERE collection=? AND floatIndex = ? ORDER BY item " + order + " LIMIT ?,?");
			getCollectionItemsStmt.setInt(1, collection);
			getCollectionItemsStmt.setDouble(2, value);
			getCollectionItemsStmt.setInt(3, skip);
			getCollectionItemsStmt.setInt(4, limit);			
			getCollectionItemsStmt.execute();
			
			ResultSet result = getCollectionItemsStmt.getResultSet();
			while(result.next()){
				items.add( new Integer(result.getInt(1)) );
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(getCollectionItemsStmt!=null){
				try {
					getCollectionItemsStmt.close();
				} catch (SQLException e) {}
			}
		}
		
		Integer[] result = new Integer[items.size()];
		return (Integer[])items.toArray(result);
	}

	
	private HashSet dirtyObjects = new HashSet();
	private HashSet dirtyObjectsWhileFlushing = new HashSet();
	private boolean flushing = false;

	
	public synchronized void addDirtyObject(StorableObject obj) {
		if(flushing){
			dirtyObjectsWhileFlushing.add(obj);
		}else{
			dirtyObjects.add(obj);
		}
	}


	
	public synchronized void flush() {
		flushing = true;
		Iterator iter = dirtyObjects.iterator();
		while(iter.hasNext()){
			try {
				StorableObject obj = (StorableObject)iter.next();
				if(obj.isDirty()){
					obj.saveToStore();
				}
			} catch (StorageException e) {
				e.printStackTrace();
			}
		}
		flushing = false;
		dirtyObjects.addAll(dirtyObjectsWhileFlushing);
		dirtyObjectsWhileFlushing.clear();
	}


	
	public synchronized void removeDirtyObject(StorableObject obj) {
		if(flushing) return;
		dirtyObjects.remove(obj);
	}



	public void removeCollectionItem(int collection, int objId) {
		PreparedStatement removeCollectionItemStmt = null;
		try {
			removeCollectionItemStmt = conn.prepareStatement("DELETE FROM items WHERE collection = ? AND item=?");
			removeCollectionItemStmt.setInt(1, collection);
			removeCollectionItemStmt.setInt(2, objId);
			removeCollectionItemStmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(removeCollectionItemStmt!=null){
				try {
					removeCollectionItemStmt.close();
				} catch (SQLException e) {}
			}
		}
	}
	

}
